#!/usr/bin/env python
# -*- coding: utf-8 -*-
# sqlserver版,用于生成全部的dal层代码

from string import Template

# ToDo: update的id要放最后处理;要能支持sqlserver和oledb的切换;s的字典改成生成模式，现在的拼合起来太麻烦了;id的处理有问题,add和update时不要生成id

#user
s = {"id":"int","weixin":"string","alipayName":"string","alipayNo":"string","bankName":"string","bankNo":"string","bankAccount":"string","intermediaryId":"int","intermediaryName":"string"};
#project
#s = {"id":"int","title":"string","brokerStr1":"string","personStr1":"string","discountStr":"string","costPrice":"string","introduction":"string","hotTip":"string","buildingId":"int","buildingName":"string","details":"string","comment":"string","mobileDetails":"string"};
#recommend
#s = {"id":"int","fullName":"string","unitPrice":"string","phoneNumber":"string","intentionalArea":"string","sumTotla":"string","buildingId":"int","buildingName":"string","type":"string","isSchoolRoom":"int","buyingTime":"string","intentionalRegion":"string","remarks":"string","userId":"int","userName":"string","userTel":"string","status":"int","instructions":"string"};
#building
# s = {"id":"int","userName":"string","userPwd":"string","name":"string","contactName":"string","contactTel":"string","address":"string","position":"string","other":"string"};

t = {"int":"SqlDbType.Int,4","string":"SqlDbType.NVarChar,255","textarea":"SqlDbType.NText"}

p = "project";

def getsType(st):
    return t.get(st);


#dal
def getparse1():

    template1 = '''using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using DTcms.DBUtility;
using DTcms.Common;

namespace DTcms.DAL
{
        /// <summary>
        /// 数据访问类:推荐客户
        /// </summary>
        public partial class ${p1}
        {
        private string databaseprefix; //数据库表名前缀
        public ${p1}(string _databaseprefix)
        {
            databaseprefix = _databaseprefix;
        }

                #region 基本方法================================
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "${p1}");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
                                        new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

                /// <summary>
                /// 增加一条数据
                /// </summary>
        public int Add(Model.${p1} model)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        #region 添加主表数据====================
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into " + databaseprefix + "${p1}(");
                        strSql.Append("${v1})");
                        strSql.Append(" values (");
                        strSql.Append("${v2})");
                        strSql.Append(";select @@IDENTITY");
                        SqlParameter[] parameters = {
    ${v3}
                        };

    ${v4}

                        //添加主表数据
                        object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), parameters); //带事务
                        model.id = Convert.ToInt32(obj);
                        #endregion

                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return 0;
                    }
                }
            }
            return model.id;
        }

                /// <summary>
                /// 更新一条数据
                /// </summary>
                public bool Update(Model.${p1} model)
                {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        #region 修改主表数据==========================
                        StringBuilder strSql=new StringBuilder();
                                    strSql.Append("update " + databaseprefix + "${p1} set ");
    ${v5}
                        strSql.Append(" where id=@id");
                                    SqlParameter[] parameters = {

    ${v3}
                        };

         ${v4}

                        DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                        #endregion


                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return false;
                    }
                }
            }
            return true;
                }

                /// <summary>
                /// 删除一条数据
                /// </summary>
                public bool Delete(int id)
                {

            List<CommandInfo> sqllist = new List<CommandInfo>();
            CommandInfo cmd = null;
            sqllist.Add(cmd);

            //删除主表
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from " + databaseprefix + "${p1} ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                                        new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;
            cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);

            int rowsAffected = DbHelperSQL.ExecuteSqlTran(sqllist);
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

                }

                /// <summary>
                /// 得到一个对象实体
                /// </summary>
        public Model.${p1} GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 ${v1}");
            strSql.Append(" from " + databaseprefix + "${p1}");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                                        new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            Model.${p1} model = new Model.${p1}();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }



                /// <summary>
                /// 获得前几行数据
                /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ${v1} ");
            strSql.Append(" FROM " + databaseprefix + "${p1} ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());

        }

        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM " + databaseprefix + "${p1}");
            if (strWhere.Trim() != "")
            {
                    strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
                #endregion

        #region 扩展方法================================
        /// <summary>
        /// 是否存在标题
        /// </summary>
        public bool ExistsTitle(string title)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "${p1}");
            strSql.Append(" where userName=@userName ");
            SqlParameter[] parameters = {
                                        new SqlParameter("@userName", SqlDbType.NVarChar,200)};
            parameters[0].Value = title;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }



        /// <summary>
        /// 返回推荐人
        /// </summary>
        public string Get${p1}(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 userName from " + databaseprefix + "${p1}");
            strSql.Append(" where id=" + id);
            string title = Convert.ToString(DbHelperSQL.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(title))
            {
                return string.Empty;
            }
            return title;
        }



        /// <summary>
        /// 返回数据总数
        /// </summary>
        public int GetCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) as H ");
            strSql.Append(" from " + databaseprefix + "${p1}");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString()));
        }


        /// <summary>
        /// 修改一列数据
        /// </summary>
        public void UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "${p1} set " + strValue);
            strSql.Append(" where id=" + id);
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }


        #endregion

        #region 私有方法================================
        /// <summary>
        /// 将对象转换为实体
        /// </summary>
        private Model.${p1} DataRowToModel(DataRow row)
        {
            Model.${p1} model = new Model.${p1}();
            if (row != null)
            {

                #region 主表信息======================

         ${v6}

                #endregion

            }
            return model;
        }
        #endregion

        #region 前台模板调用方法========================
        /// <summary>
        /// 根据视图显示前几条数据
        /// </summary>
        public DataSet GetList(string channel_name, int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * FROM view_channel_" + channel_name);
            strSql.Append(" where datediff(d,add_time,getdate())>=0");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 根据视图获取总记录数
        /// </summary>
        public int GetCount(string channel_name, int category_id, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" count(1) FROM view_channel_" + channel_name);
            strSql.Append(" where datediff(d,add_time,getdate())>=0");
            if (category_id > 0)
            {
                strSql.Append(" and category_id in(select id from " + databaseprefix + "recommend_category where class_list like '%," + category_id + ",%')");
            }
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            return Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString()));
        }

        /// <summary>
        /// 根据视图显示前几条数据
        /// </summary>
        public DataSet GetList(string channel_name, int category_id, int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * FROM view_channel_" + channel_name);
            strSql.Append(" where datediff(d,add_time,getdate())>=0");
            if (category_id > 0)
            {
                strSql.Append(" and category_id in(select id from " + databaseprefix + "recommend_category where class_list like '%," + category_id + ",%')");
            }
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 根据视图获得查询分页数据
        /// </summary>
        public DataSet GetList(string channel_name, int category_id, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM view_channel_" + channel_name);
            strSql.Append(" where datediff(d,add_time,getdate())>=0");
            if (category_id > 0)
            {
                strSql.Append(" and category_id in(select id from " + databaseprefix + "recommend_category where class_list like '%," + category_id + ",%')");
            }
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }

        /// <summary>
        /// 获得关健字查询分页数据(搜索用到)
        /// </summary>
        public DataSet GetSearch(string channel_name, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,channel_id,call_index,title,zhaiyao,add_time,img_url from " + databaseprefix + "recommend");
            strSql.Append(" where id>0");
            if (!string.IsNullOrEmpty(channel_name))
            {
                strSql.Append(" and channel_id=(select id from " + databaseprefix + "channel where [name]='" + channel_name + "')");
            }
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }

        #endregion
    }
}

''';

    code = Template(template1);
    p1 = p;
    n = 0;
    v1="";
    v2="";
    v3="";
    v4="";
    v5="";    
    v6="";
    
    for i in s:
         if i == "id": continue;
         ty = getsType(s.get(i));
         ii =  len(s) -1 - n;
         v1 = i + "," + v1;
         v2 = "@"+ i + "," + v2;
         v3 = '''new SqlParameter("@''' + i  +'''", '''+ ty+'''),\n''' + v3;
         v4 = "parameters["+ bytes(ii) +"].Value = model." + i + ";\n" + v4;
         v5 =  '''strSql.Append("'''+i + '''=@'''+i+''',");\n''' + v5;
         v6_t = '''if (row["${v61}"] != null && row["${v61}"].ToString() != "")
                {
                    ${v62}
                }\n''';
         if(s.get(i)=="int"):
             v62 = "model." + i + " =  int.Parse(row[\""+ i +"\"].ToString());";
         elif(s.get(i)=="DateTime"):
             v62 = "model." + i + " =  DateTime.Parse(row[\""+ i +"\"].ToString());";
         else:
             v62 = "model." + i +" = row[\"" + i + "\"].ToString();";
         code_v6 = Template(v6_t);
         v63 = code_v6.substitute({"v62":v62,"v61":i});
         v6 = v6 + v63;
         n = n + 1;

    v1 = v1[:-1];
    v2 = v2[:-1];
    v3 = v3[:-1];
    s1 = code.substitute({"p1":p1,"v1":v1,"v2":v2,"v3":v3,"v4":v4,"v5":v5,"v6":v6})
    print s1;

# 根据传递的变量名，生成一句独立的语句,还在测试
# s1 的格式["int","字段的中文名"]
def parse1(s1):
    t='''new SqlParameter("@${v1}", ${v2}),''';
    code_v6 = Template(t);
    v1 =  s1;
    v2 = s1[0];   #类型

    return code_v6.substitute({"v1":v1,"v2":v2});

getparse1();

